<?php
 if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mcoupon extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function getData()
    {
        $index = $this->getParamInt('index');
        if(!in_array($index, array(null, '', '0')))
        {
            $limit_sql = " LIMIT  ".$index.", 10";
        }else{
            $limit_sql = " LIMIT  10";
        }
        
        $sql = 'SELECT ecp.id AS ecpid, ecp.title AS ecptitle, ecp.img_qrcode as img_qrcode, ecp.img_thumbnail AS img_thumbnail, ecp.sale_off, ecp.amount,ecp.type_sl,  FROM_UNIXTIME(ecp.date_user, "%d/%m/%Y") AS expiration_date, ecp.`condition`, ecp.content_sale_off AS short_description FROM ecoupon AS ecp WHERE ecp.active = 1';
        
		$num_rows = $this->db->query($sql.$limit_sql)->num_rows();
		if($num_rows == 0)
		{
			return (object)array('status' => 404,'content' => array(), 'description' => 'Danh sách coupon rỗng');
		}
        $runSql = $this->db->query($sql.$limit_sql)->result_object();
        
        foreach($runSql AS $el=>$le)
        {
			
			if(in_array($le->condition, array(null, '', '0')))
			{
				$runSql[$el]->condition = '';
			}else{
				$runSql[$el]->condition = $le->condition;
			}
            if(!in_array($le->ecpid, array(null, '', '0')) && $le->type_sl != 0)
            {
                //die('SELECT CONCAT(r.`name`,": ",ra.address) AS address FROM ecoupon_key AS eck INNER JOIN restaurant AS r ON r.id = eck.id_restaurant INNER JOIN restaurant_address AS ra ON ra.restaurant_id = r.id WHERE eck.id_ecoupon = '.$le->ecpid);
                $runSql[$el]->_address = $this->db->query('SELECT CONCAT(r.`name`,": ", ra.address) AS address FROM ecoupon_key AS eck  INNER JOIN restaurant AS r ON r.id = eck.id_restaurant RIGHT JOIN restaurant_address AS ra ON ra.restaurant_id = r.id WHERE eck.id_ecoupon = '.$le->ecpid)->result_object();
            }else{
                $runSql[$el]->_address = (object)array();
            }
        }
        return (object)array('status' => 200,'content' => $runSql, 'description' => 'Danh sách coupon');
    }
    
    public function getDetail()
    {
                 
        $id         = $this->getParamInt('id');
        $user_id    = $this->getParamInt('user_id');
        $zone       =$this->getParamInt('zone');
        if(in_array($zone, array('',null)) || in_array($id, array('',null)) )return (object)array('status' => 203,'content' => array(), 'description' => 'Zone hoặc id bị thiếu');
        $sql =   "SELECT ecp.id AS ecpid, ecp.title AS ecptitle,ecp.img_qrcode, ecp.img_thumbnail AS img_thumbnail, ecp.amount_available, ecp.content," 
                ."ecp.content_sale_off AS brief, ecp.sale_off, ecp.amount,"  
                .'FROM_UNIXTIME(ecp.date_user, "%d/%m/%Y") AS expiration_date, ecp.`condition` ,'
                ."GROUP_CONCAT(DISTINCT CONVERT(restaurant.id, CHAR(8)) SEPARATOR ',') as id_restaurant "
                ."FROM ecoupon AS ecp "
                ."INNER JOIN ecoupon_key ON ecp.id= ecoupon_key.id_ecoupon " 
                ."INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id " 
                ."AND ecp.id = '$id' AND restaurant.delete_status  = 0 ";
        $data=$this->db->query($sql);
        $num_rows = $data->num_rows();
        if($num_rows == 0)
        {
            return (object)array('status' => 404,'content' => array(), 'description' => 'Không tồn tại coupon này', 'data_cp' => array());
        }
        $runSql = $data->row();
        if( in_array($user_id,array('',null,0,'0')) ){
                $getAddress=$this->addressResDb($runSql->id_restaurant, $zone);
                return (object)array('status' => 200,'content' => $runSql, 'description' => 'Chi tiết coupon', 'data_cp' => array(),'data_address'=>$getAddress->data);
        }else{
                $data_ = $this->db->query('SELECT * FROM ecoupon_user WHERE user_id = '.$user_id.' AND coupon_id = '.$id.' AND active = 1 ');
                if( $data_->num_rows() > 0 ){
                        $data_cp = array( 'user_id' => $user_id, 'code_coupon' => $data_->row()->code_coupon_copy != null ?$data_->row()->code_coupon_copy:$data_->row()->code_coupon, 'active' => $data_->row()->active, 'created' => $data_->row()->created );
                        $getAddress=$this->addressResDb($runSql->id_restaurant, $zone);
                        return (object)array('status' => 201,'content' => $runSql, 'description' => 'Chi tiết coupon', 'data_cp' => $data_cp,'data_address'=>$getAddress->data);
                }else{
                        $getAddress=$this->addressResDb($runSql->id_restaurant, $zone); 
                        return (object)array('status' => 200,'content' => $runSql, 'description' => 'Chi tiết coupon', 'data_cp' => array(),'data_address'=>$getAddress->data );
                }
        }
    }

    public function reviewCouponDB($coupon_id,$user_id,$name,$star,$content){
        
        $check = $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id = '.$user_id)->num_rows();
        if($check > 0)
        {
            $checkUser = $this->db->query('SELECT id FROM member WHERE id = '.$user_id.' AND active = 2')->num_rows();
            if($checkUser > 0)
            {
                return (object)array('status' => 203, 'description' => 'Tài khoản này đang bị khóa');
            }
            
            $data = array (
                'coupon_id'     => $coupon_id,
                'user_id'       => $user_id,
                'name'          => $name,
                'star'          => $star,
                'content'       => $content,
                'created'       => time(),
                'improved'      => 0
                );
                
            $run = $this->db->insert('ecoupon_review', $data);
            if($run){
                return (object)array('status' => 200, 'description' => 'Thêm thành công');
            }else{
                return (object)array('status' => 201, 'description' => 'Thêm thất bại');
            }
        }else{
            return (object)array('status' => 202, 'description' => 'Người dùng chưa sử dụng coupon này');
        }
        
    }
    
    public function getReviewCoupon(){
        
        $id = $this->getParamInt('id');
        $index = $this->getParamInt('index');
        if(!in_array($index, array(null, '', '0')))
        {
            $limit_sql = " LIMIT  ".$index.", 10";
        }else{
            $limit_sql = " LIMIT  10";
        }
        
        $sql = $this->db->query('SELECT er.`name`, er.star, er.content, m.username, m.fullname FROM ecoupon_review AS er INNER JOIN member AS m ON er.user_id = m.id WHERE er.improved = 1 AND er.coupon_id = '.$id.$limit_sql);
		$data = $sql->result_object();
		$num_rows = $sql->num_rows();
		if($num_rows == 0)
		{
			return (object)array('status' => 404,'content' => array(), 'description' => 'Không tồn tại danh sách bình luận cho coupon này');
		}
        return (object)array('status' => 200,'content' => $data, 'description' => 'Danh sách coupon');
    }

	
//	public function userGetCoupon($user_id,$coupon_id)
//        {
//		if ( 
////                $this->db->query('SELECT id FROM member WHERE id = '.$user_id)->num_rows() == 0 || 
////		$this->db->query('SELECT id FROM ecoupon WHERE id = '.$coupon_id.' AND `created` < "'.date('Y-m-d H:i:s').'"' )->num_rows() == 0 || 
////		( $this->db->query('SELECT amount FROM ecoupon WHERE id = '.$coupon_id)->row()->amount) <= ($this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available) ||
////		( $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id IS NULL ')->num_rows() == 0 )
//		){
//			return (object)array('status' => 201, 'description' => 'Người dùng không tồn tại/Coupon không tồn tại/Coupon hết hạn/Coupon đã sử dụng hết');
//		}else{ 
//                    echo "dấdf";die;
//			if ( $this->db->query('SELECT id FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id = '.$user_id.' AND active != 2' )->num_rows() != 0 ){
//				$code_coupon = $this->db->query('SELECT code_coupon FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id = '.$user_id)->row()->code_coupon;
//				return (object)array('status' => 202, 'description' => 'Coupon này đã nhận', 'code_coupon' => $code_coupon );
//			}
//			$id_ecoupon_user = $this->db->query('SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ')->row()->id;
//			$code_coupon = $this->db->query('SELECT id, code_coupon FROM ecoupon_user WHERE coupon_id = '.$coupon_id.' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ')->row()->code_coupon;
//			
//			//$data = array ( 'user_id' => $user_id, 'coupon_id' => $coupon_id, 'code_coupon' => $code_coupon , 'active' => 1, 'created' => date('Y-m-d H:i:s') );
//			$run = $this->db->update('ecoupon_user', array( 'user_id' => $user_id, 'active' => 1, 'created' => date('Y-m-d H:i:s') ) , 'id = '.$id_ecoupon_user);
//			
//			$this->sendMailUser($user_id, $code_coupon, $id_ecoupon_user);
//            if($run){
//				// update so coupon da dung
//				$amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available;
//				$amount_available = (int)$amount_available + 1;
//				$this->db->update('ecoupon', array( 'amount_available' => $amount_available ) , 'id = '.$coupon_id);
//                return (object)array('status' => 200, 'description' => 'Thêm thành công', 'code_coupon' => $code_coupon );
//            }else{
//                return (object)array('status' => 203, 'description' => 'Thêm thất bại');
//            }
//		}
//	}
    
    
    /**
     * Lấy coupon code(test user_id=911,coupon_id=80(ggift la 68)http://localhost/wicard/api/get-code-coupon?coupon_id=80&user_id=911
     * 
     * @param int $user_id
     * @param int $coupon_id
     * @return object
     */
     public function getCodeCouponDb($user_id,$coupon_id)
     {
        //Chinh lai cai api nay...neu coupon ko phai gpeople thi nhu cu,update,tra ma
        //neu coupon la cua gpeople thi up ben ggift,upben minh ,tra  ve ma
        //Kiểm tra code
        $result=$this->checkCodeCouponDb($user_id,$coupon_id);
//        var_dump($result);die;
        if($result->status != 200) return $result;      // nếu không phải chưa nhận thì return
        $result=$this->db->query('SELECT ecoupon_user.id,ecoupon_user.code_coupon,ecoupon_user.code_coupon_copy,ecoupon_user.is_wicity FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id IS NULL ORDER BY id ASC LIMIT 1 ');
        if($result->num_rows() == 0) return (object) array('status' => 204, 'description' => 'Coupon đã full người dùng');
        $row_ecoupon_user = $result->row();
        
        //Nếu là của wicity
        if($row_ecoupon_user->is_wicity == 1) 
        {
          //Lấy email và code coupon bên wicard gửi sang wicity
            $user=$this->db->query("SELECT * FROM member WHERE id = '$user_id'")->row();
            $email = $user->email;
            $code_coupon=$row_ecoupon_user->code_coupon;
            //gọi api insert vào ecoupon_user bên ggift    
            $url2="sync-get-coupon?email=".$email."&code_coupon=".$code_coupon;  
            $url1="http://wicity.vn/homepage/";
            $apiCoupon=json_decode(file_get_contents($url1.$url2));
            //Nếu thất bại thì return 
            if($apiCoupon->status != 200)
            {
                return (object) array('status' => 203, 'description' => 'Lấy coupon thất bại');   
            }
            //Nếu thành công bên wicity thì mình insert về bên mình ,+1 amount,trả code ra màn hình
            $data = array('user_id' => 1,'active' => 1,'created' => date('Y-m-d H:i:s'));
            $this->db->where('id', $row_ecoupon_user->id);
            $result_update=$this->db->update('ecoupon_user', $data);     
            //thêm số lượng
            $amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available;
            $amount_available = (int)$amount_available + 1;
            $this->db->update('ecoupon', array( 'amount_available' => $amount_available ) , 'id = '.$coupon_id);
            return (object) array('status' => 200, 'description' => 'Thành công', 'code_coupon' => $apiCoupon->code_coupon);
        }
        
        if($row_ecoupon_user->code_coupon_copy != 1) 
        {
            
            $data = array('user_id' => $user_id,'active' => 1,'created' => date('Y-m-d H:i:s'));
            $this->db->where('id', $row_ecoupon_user->id);
            $result_update=$this->db->update('ecoupon_user', $data);      
            if($result_update)
            {
                $code=$row_ecoupon_user->code_coupon;
                $amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available;
                $amount_available = (int)$amount_available + 1;
                $this->db->update('ecoupon', array( 'amount_available' => $amount_available ) , 'id = '.$coupon_id);
                return (object) array('status' => 200, 'description' => 'Thành công', 'code_coupon' => $code);
            }
            else
            {
                return (object) array('status' => 203, 'description' => 'Lấy coupon thất bại,thử lại.');   
            }
        }
        
        //đây là trường hợp nhận coupon của gpeople
            //đồng bộ user
            $user=$this->db->query("SELECT * FROM member WHERE id = '$user_id'")->row();
            $email = $user->email;
            $fullname = $user->fullname;
            $mobile = $user->mobile;
            $url1 = "register?username=".urlencode($email)."&password=".$mobile."&mobile=".$mobile."&fullname=".urlencode($fullname)."&gender=1&dateofbirth=1991-12-20&job_id=5&province_id=1&district_id=1&nationality=241&language=1";
            $apiUser=json_decode(file_get_contents(URL.$url1));
            //update bên coupon bên ggift với user vừa đồng bộ và coupon bên ggift
            $couponCopy=$this->db->query("SELECT id_copy FROM ecoupon WHERE id = '$coupon_id'")->row()->id_copy;
            $id=$apiUser->id;
            //gọi api insert vào ecoupon_user bên ggift    
            $url2="user-get-ecouponother?user_id=".$id."&coupon_id=".$couponCopy;  
            $apiCoupon=json_decode(file_get_contents(URL.$url2));
            
            if($apiCoupon->status != 200)
            {
                return (object) array('status' => 203, 'description' => 'Lấy coupon thất bại');   
            }
            //Thành công bên ggift thì mình insert về bên mình ,+1 amount,tra code ra màn hình
            $data = array('user_id' => $user_id,'active' => 1,'created' => date('Y-m-d H:i:s'),'code_coupon_copy'=>$apiCoupon->code_coupon);
            $this->db->where('id', $row_ecoupon_user->id);
            $result_update=$this->db->update('ecoupon_user', $data);     
            //thêm số lượng
            $amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available;
            $amount_available = (int)$amount_available + 1;
            $this->db->update('ecoupon', array( 'amount_available' => $amount_available ) , 'id = '.$coupon_id);
            return (object) array('status' => 200, 'description' => 'Thành công', 'code_coupon' => $apiCoupon->code_coupon);
        
        
    }
    
    /**
     * Search coupoun,member theo zone và keyword
     * Theo zone thì lọc theo title Coupoun và mới nhất lên đầu,member loc theo username,fullname ,email,mobile
     * 
     * @param string $keyword từ khóa search
     * @param int $limit      kết thúc limit   
     * @param int $stt        bắt đầu limit
     * @param int $zone       id_zone mã tỉnh
     * @return object
     */
    public function searchCouponDb($keyword,$limit,$pageCoupon,$pageIncentive,$zone)
    {
		If($pageCoupon < 1 || $pageIncentive < 1  || in_array($limit, array(null, '', '0'))  ||  in_array($zone, array(null, '', '0')) )
			{
				 return (object) array('status' => 201, 'description' => 'Tham số không đúng'); 
			}
			$stt_coupon=$limit*($pageCoupon-1);
			$stt_incentive=$limit*($pageIncentive-1);
				//$stt_member=$limit*($pageMember-1);
        	$sql_limit_coupon = ' LIMIT '.$stt_coupon.','.$limit;
			$sql_limit_incentive = ' LIMIT '.$stt_incentive.','.$limit;
        	//$sql_limit_member = 'LIMIT '.$stt_member.','.$limit;
                $keyword='%'.$keyword.'%';
                $now=time();
                //Search coupon with title coupon
		$query = "SELECT ecoupon.id as id_coupon ,ecoupon.condition,ecoupon.content,ecoupon.content_sale_off,ecoupon.title AS name_coupon,ecoupon.img_thumbnail AS image_coupon,"."\n"
                        . "ecoupon.sale_off as money_coupon,ecoupon.date_user as expired_time, "."\n"
                        . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,GROUP_CONCAT(DISTINCT restaurant.name SEPARATOR ' , ') as name_restaurant ,GROUP_CONCAT(DISTINCT restaurant.id SEPARATOR ',') as id_restaurant "."\n"
                        . "  FROM ecoupon INNER JOIN ecoupon_key ON ecoupon.id= ecoupon_key.id_ecoupon  "."\n"
                        . "INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id"."\n"
                        . "INNER JOIN restaurant_address ON restaurant.id=restaurant_address.restaurant_id "."\n"
                        . "WHERE restaurant_address.id_zone  = $zone "."\n"
                        . "AND ecoupon.date_user > '$now' "."\n"
                        . "AND ecoupon.amount_available < ecoupon.amount "."\n"
                        . "AND ecoupon.title_ascii LIKE '$keyword' "."\n"
                        . "AND ecoupon.delete_status = 0 "."\n"
                        . "AND restaurant.delete_status = 0 "."\n"
                        ." Group by ecoupon.id "
                        . " order by ecoupon.id DESC " ;//.$sql_limit;
//                var_dump($query);die;
		$coupons =  $this->db->query($query.$sql_limit_coupon)->result_array();  
                $total_coupon=$this->db->query($query)->num_rows();  
                foreach ($coupons as $key => $coupon)
                {
                    $end= $coupon['expired_time']; 
                    $content=  str_replace(array('<div style="font-family: OpenSans;">','</div>'),'',$coupon['content']);
//                    $end=1427621483;
//                    $distance_time= $this->timeSince($start,$end,$units = 2);
                    $distance_time= $this->timeDiscount($end);
                    $coupons[$key]['expired_time']=date('d/m/Y',$end);
//                    $coupons[$key]['rest_time']=$distance_time;
                    $coupons[$key]['rest_time']=date('d/m/Y',$end);
                    $coupons[$key]['content']=$content;
                }
                //$query_member="SELECT * from member WHERE username LIKE '$keyword' "
                  //      . "or fullname LIKE '$keyword' or email LIKE '$keyword' or mobile LIKE '$keyword' ";//.$sql_limit;
                //$member =  $this->db->query($query_member.$sql_limit_member)->result_array();
                //$total_member=$this->db->query($query_member)->num_rows();
                                $query_incentive=" SELECT ir.id,ir.title,ir.image,ir.slide_image ,ir.summary,ir.content"
                                                . " FROM incentive_restaurant as ir,incentive_zone_key as izk "
                                                . "WHERE ir.id=izk.id_incentive AND izk.id_zone = '$zone' "
                                                . "AND ir.title_ascii LIKE '%$keyword%' ORDER by ir.id DESC";
//				$query_incentive = "SELECT ir.*,izk.* FROM incentive_restaurant AS ir INNER JOIN incentive_zone_key AS izk ON izk.id_incentive = ir.id WHERE ir.title_ascii LIKE '%".$keyword."%' AND id_zone = ".$zone; 
                $incentive =  $this->db->query($query_incentive.$sql_limit_incentive)->result_array();
                $total_incentive = $this->db->query($query_incentive)->num_rows();
                $data['list_coupon']=$coupons;
				$data['list_incentive']=$incentive;
                //$data['list_member']=$member;
                $data['total_coupon']=$total_coupon;
				$data['total_incentive']=$total_incentive;
                //$data['total_member']=$total_member;
                //Lấy tổng số bản ghi của member và coupon
                if(count($coupons) == 0 && count($incentive) == 0)return (object) array('status' => 204, 'description' => 'Search thành công,không tìm thấy data', 'data' => $data); 
                if(count($coupons) == 0)return (object) array('status' => 203, 'description' => 'Search thành công,không tìm thấy coupon', 'data' => $data); 
                if(count($incentive) == 0)return (object) array('status' => 202, 'description' => 'Search thành công,không tìm thấy tin ưu đãi', 'data' => $data); 
                return (object) array('status' => 200, 'description' => 'Search thành công,tìm thấy tin ưu đãi và coupon', 'data' => $data); 
    } 
    
    /**
     * Lấy coupoun theo zone
     * 
     * @param type $limit
     * @param type $stt
     * @param type $zone
     * @return type
     */
    public function getCouponDb($limit,$page,$zone)
    {
                If($page< 1 || in_array($limit, array(null, '', '0'))  || in_array($zone, array(null, '', '0')) )
                {
                     return (object) array('status' => 201, 'description' => 'Tham số không đúng'); 
                }
                $stt=$limit*($page-1);
                $sql_limit = 'LIMIT '.$stt.','.$limit;
                $now=time(); //GROUP_CONCAT(restaurant.name SEPARATOR ',')
                $query="   SELECT ecoupon.id as id_coupon ,ecoupon.condition,ecoupon.content,ecoupon.content_sale_off,ecoupon.title AS name_coupon,ecoupon.img_thumbnail AS image_coupon,"."\n"
                        . "ecoupon.sale_off as money_coupon,ecoupon.date_user as expired_time, "."\n"
                        . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,GROUP_CONCAT(DISTINCT restaurant.name SEPARATOR ' , ') as name_restaurant,GROUP_CONCAT(DISTINCT restaurant.id SEPARATOR ',') as id_restaurant "."\n"
                        . "  FROM ecoupon INNER JOIN ecoupon_key ON ecoupon.id= ecoupon_key.id_ecoupon  "."\n"
                        . "INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id "."\n"
                        . "INNER JOIN restaurant_address ON restaurant.id=restaurant_address.restaurant_id "."\n"
                        . "WHERE restaurant_address.id_zone  = $zone "."\n"
                        . "AND ecoupon.date_user > '$now' "."\n"
                        . "AND ecoupon.amount_available < ecoupon.amount "."\n"
                        . "AND ecoupon.delete_status = 0 "."\n"
                        . "AND restaurant.delete_status = 0 "."\n"
                        ." Group by ecoupon.id"
                        . " order by ecoupon.id DESC " ;//.$sql_limit;
//                var_dump($query);die;
		$coupons =  $this->db->query($query.$sql_limit)->result_array();  
                $total =    $this->db->query($query)->num_rows();  
                foreach ($coupons as $key => $coupon)
                {
                    $end= $coupon['expired_time']; 
                    $content=  str_replace(array('<div style="font-family: OpenSans;">','</div>'),'',$coupon['content']);
//                    $content=$coupon['content'];
//                    $end=1427621483;
//                    $distance_time= $this->timeSince($start,$end,$units = 2);
                    $distance_time= $this->timeDiscount($end);
                    $coupons[$key]['expired_time']=date('d/m/Y',$end);
//                    $coupons[$key]['rest_time']=$distance_time;
                    $coupons[$key]['rest_time']=date('d/m/Y',$end);
                    $coupons[$key]['content']=$content;
                    
                }
                $data['list_coupon']=$coupons;
                $data['total']=$total;
                if(count($coupons) == 0) (object) array('status' => 202, 'description' => 'Search thành công,ko thấy data', 'data' => $data);
                return (object) array('status' => 200, 'description' => 'Search thành công,có data', 'data' => $data);
                //return (object)$new_coupons;
    }
    
    /**
     * Kiểm tra code coupon đã nhận,kích hoạt hay chưa nhận
     * 
     * @param int $user_id
     * @param int $coupon_id
     * @return object
     */
    public function checkCodeCouponDb($user_id,$coupon_id)
    {
        
        //Kiểm tra user tồn tại ko
        if($this->db->query('SELECT id FROM member WHERE id = '.$user_id)->num_rows() == 0)
        {
           return (object) array('status' => 202, 'description' => 'User không tồn tại', 'user_id' => $user_id); 
        }
        //Kiểm tra coupon tồn tại không 
        if($this->db->query('SELECT id FROM ecoupon WHERE id = '.$coupon_id)->num_rows() == 0)
        {
           return (object) array('status' => 202, 'description' => 'Coupon không tồn tại', 'coupon_id' => $coupon_id); 
        }
        //Kiểm tra coupon nếu kích hoạt rồi /đã nhận với người dùng này thì trả về thông báo và code_coupon.
        $result = $this->db->query('SELECT * FROM ecoupon_user WHERE coupon_id = ' . $coupon_id . ' AND user_id = ' . $user_id);
        if ($result->num_rows() != 0)
        {
            if($result->row()->active == 1)
            return (object) array('status' => 201, 'description' => 'Coupon đã nhận', 'code_coupon' => $result->row()->code_coupon_copy == null ? $result->row()->code_coupon:$result->row()->code_coupon_copy );
            return (object) array('status' => 201, 'description' => 'Coupon đã kích hoạt', 'code_coupon' => $result->row()->code_coupon_copy == null ? $result->row()->code_coupon:$result->row()->code_coupon_copy);
        }
        return (object) array('status' => 200, 'description' => 'User chưa nhận coupon');
    }
   
    private function sendMailUser($user_id, $code_coupon, $id_ecoupon_user)
    {
		$db = $this->db->query('SELECT username,fullname FROM member WHERE id = '.$user_id.' LIMIT 1')->row();
        $datacp = $this->db->query('SELECT e.title,e.note_email,e.subject_email FROM ecoupon AS e LEFT JOIN ecoupon_user AS eu ON e.id = eu.coupon_id WHERE eu.id = '.$id_ecoupon_user.' LIMIT 1')->row(); 
        $config = array(
						'protocol' => 'smtp',
						'smtp_host' => $this->_smtp_host,
						'smtp_user' => $this->_smtp_user,
						'smtp_pass' => $this->_smtp_pass,
						'mailtype' => 'html'
                    );
        $this->load->library('email',$config);
        $body    = "<img src='http://125.212.192.114/ggift/data/images/other/mail-header-gg.jpg' style='width: 825px;'/><br/><br/>Chúc mừng bạn đã đăng ký nhận ".$datacp->title." thành công! <br />Mã E-coupon của bạn là: <b>".$code_coupon."</b><br /><br />".html_entity_decode(stripslashes($datacp->note_email), ENT_QUOTES, "UTF-8")."<br/><br/><img src='http://125.212.192.114/ggift/data/images/other/mail-footer-gg.jpg' style='width: 825px;'/>"; 
        $this->email->from($this->_smtp_user, 'G-People');
        $this->email->to($db->username);    
        $this->email->subject($datacp->subject_email);
        $this->email->message($body);    
        if($this->email->send()){
        }
    }
	
    public function checkDelCouponUser(){

            $data = $this->db->query('SELECT id,created,coupon_id FROM ecoupon_user WHERE active = 1')->result_object();
            foreach ( $data AS $el=>$le ){
                    $time_created = strtotime(date('Y-m-d', strtotime($le->created)));
                    $time = strtotime("+5 day", $time_created);
                    if( time() > $time){ // het han
                            $this->db->update('ecoupon_user', array( 'user_id' => null, 'active' => (int)0, 'created' => null  ) , 'id = '.$le->id);
                            $amount_available = (int)$this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$le->coupon_id)->row()->amount_available - 1;
                            $this->db->update('ecoupon', array('amount_available' => $amount_available),'id ='.$le->coupon_id);
                    }
            }
            return 200;
    }
    
    /**
     * Lấy tất cả coupon ứng với user 
     * 
     * @param int $user_id
     * @param int $limit
     * @param int $stt
     * @return object
     */
    public function myCouponDb($user_id,$limit,$page)
    {
        If($page< 1 || in_array($user_id, array(null, '', '0')) || in_array($limit, array(null, '', '0')))
        {
             return (object) array('status' => 201, 'description' => 'Tham số không đúng'); 
        }
        $stt=$limit*($page-1);
        $sql_limit = 'LIMIT '.$stt.','.$limit;
        $query="SELECT ecoupon.id as id_coupon ,ecoupon_user.code_coupon,ecoupon.condition,ecoupon.content,ecoupon.content_sale_off,ecoupon.title AS name_coupon,ecoupon.img_thumbnail AS image_coupon,"."\n"
                . "ecoupon.sale_off as money_coupon,ecoupon.date_user as expired_time, "."\n"
                . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,GROUP_CONCAT(DISTINCT restaurant.name SEPARATOR ' , ') as name_restaurant ,GROUP_CONCAT(DISTINCT restaurant.id SEPARATOR ',') as id_restaurant ,ecoupon_user.active"."\n"
                . " FROM ecoupon INNER JOIN ecoupon_user ON ecoupon.id= ecoupon_user.coupon_id  "."\n"
                ." INNER JOIN ecoupon_key ON ecoupon.id= ecoupon_key.id_ecoupon  "."\n"
                ." INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id "."\n"
                ." WHERE  ecoupon_user.user_id = $user_id" 
                . " AND ecoupon.delete_status = 0 "."\n"
                . "AND restaurant.delete_status = 0 "."\n"
                . " Group by ecoupon.id "
                . " order by ecoupon_user.created DESC " ;//.$sql_limit;
        $coupons =  $this->db->query($query.$sql_limit)->result_array(); 
        $query3= "SELECT ecoupon.id "
                . "FROM ecoupon INNER JOIN ecoupon_user ON ecoupon.id= ecoupon_user.coupon_id  "."\n"
                . "WHERE  ecoupon_user.user_id = $user_id " 
                . "AND ecoupon.delete_status = 0 "."\n "
                . "Group by ecoupon.id "
                . "order by ecoupon_user.created DESC " ;
        $total=$this->db->query($query3)->num_rows; 
        foreach ($coupons as $key => $coupon)
        {
            $end= $coupon['expired_time']; 
            $content=  str_replace(array('<div style="font-family: OpenSans;">','</div>'),'',$coupon['content']);
            $distance_time= $this->timeDiscount($end);
            $coupons[$key]['expired_time']=date('d/m/Y',$end);
//            $coupons[$key]['rest_time']=$distance_time;
            $coupons[$key]['rest_time']=date('d/m/Y',$end);
            $coupons[$key]['content']=$content;
            //Nếu bị khóa hoặc ( hết hạn và chưa sử dụng)->không hoạt động
            if( $coupons[$key]['active'] == '0' || ($distance_time == 'Hết hạn' && $coupons[$key]['active'] == '1' )){
                $coupons[$key]['status'] = "Không hoạt động";
                $coupons[$key]['status_number'] = "1";
            }
            //Nếu đã sử dụng ->đã sử dụng(không quan tâm hạn)
            if( $coupons[$key]['active'] == '2'  ){
                $coupons[$key]['status'] = "Đã sử dụng";
                $coupons[$key]['status_number'] = "2";
            }
            //Nếu chưa sử dụng và chưa hết hạn ->chưa sử dụng
            if( $coupons[$key]['active'] == '1' && $distance_time != 'Hết hạn' ){
                $coupons[$key]['status'] = "Chưa sử dụng";
                $coupons[$key]['status_number'] = "3";
            }
            //Get code trả về client(new)
//            $coupons[$key]['code_coupon']=$this->db->query('SELECT * FROM ecoupon_user WHERE coupon_id = ' . $coupons[$key]['id_coupon'] . ' AND user_id = ' . $user_id)->row()->code_coupon;
        }
//        var_dump($coupons);die;
        $data['list_coupon']=$coupons;
        $data['total']=$total;
        if($total == 0) return (object)array('status' => 202, 'description' => 'Thành công,ko có data','data'=>$data);
        return (object)array('status' => 200, 'description' => 'Thành công,có data','data'=>$data);
//        return (object)$new_coupons;
    }
    
    /**
     * Delete coupon 
     * 
     * @param int $user_id
     * @param int $coupon_id
     * @return object 
     */
    public function deleteCouponDB($user_id,$coupon_id){
//       //Kiểm tra user tồn tại ko
//        if($this->db->query('SELECT id FROM member WHERE id = '.$user_id)->num_rows() == 0)
//        {
//           return (object) array('status' => 202, 'description' => 'User không tồn tại', 'user_id' => $user_id); 
//        }
//        //Kiểm tra coupon tồn tại không 
//        if($this->db->query('SELECT id FROM ecoupon WHERE id = '.$coupon_id)->num_rows() == 0)
//        {
//           return (object) array('status' => 202, 'description' => 'Coupon không tồn tại', 'coupon_id' => $coupon_id); 
//        }
        //Kiểm tra coupon này có phải của user này không.
        $query="SELECT * FROM ecoupon_user WHERE coupon_id = $coupon_id AND user_id = $user_id";
        $result=$this->db->query($query);
        if($result->num_rows()==0)
        {
           return (object) array('status' => 202, 'description' => 'Coupon không phải của user');  
        }
        
        //Nếu đã sử dụng nhưng lại xóa thì vẫn giử lại,ko giảm số lượng đã đặt, đổi trạng thái active=3(đã sử dụng nhưng bị xóa)
        if($result->row()->active == 2)
        {
            $this->db->where('id', $result->row()->id);
            $result_update=$this->db->update('ecoupon_user', array('active' => 3));            
        }
        else
        {
            //Chưa sử dụng thì hết hạn hay ko hay bị khóa hay không thì nó cứ chuyển thành null rồi lượng đặt nó cứ trừ đi đỡ phải xét
            $data = array('user_id' => NULL,'active' => NULL,'created' => NULL);
            $this->db->where('id', $result->row()->id);
            $result_update=$this->db->update('ecoupon_user',$data);
             //Chỉnh amout available in coupon
            $amount_available = (int)$this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$result->row()->id)->row()->amount_available - 1;
            $result_update=$this->db->update('ecoupon', array('amount_available' => $amount_available),'id ='.$result->row()->id);
        }
        // Kiểm tra Update thành công thì gửi kết quả qua email hoặc sdt....
        if($result_update)
        {
            return (object) array('status' => 200, 'description' => 'Delete Thành công');
        }
        return (object) array('status' => 201, 'description' => 'Delete Thất bại');   
        
    }
    
    public function hotCouponDb($limit,$page,$zone)
    {
        If($page< 1 || in_array($limit, array(null, '', '0')) || in_array($zone, array(null, '', '0')))
        {
             return (object) array('status' => 203, 'description' => 'Tham số không đúng'); 
        }
        $stt=$limit*($page-1);
        $type_coupon=2;
        $now=date('Y-m-d');
        //Lấy coupon hot 
         $query=" SELECT ecoupon.id as id_coupon ,hp.top,ecoupon.condition,ecoupon.content,ecoupon.content_sale_off,ecoupon.title AS name_coupon,ecoupon.img_thumbnail AS image_coupon,
                  ecoupon.sale_off as money_coupon,ecoupon.date_user as expired_time, 
                  ecoupon.amount,ecoupon.amount_available as coupon_dadat,GROUP_CONCAT(DISTINCT restaurant.name SEPARATOR ' , ') as name_restaurant ,GROUP_CONCAT(DISTINCT restaurant.id SEPARATOR ',') as id_restaurant 
                    FROM ecoupon INNER JOIN hot_coupon as hc ON ecoupon.id=hc.id_coupon 
                     INNER JOIN restaurant ON hc.id_restaurant=restaurant.id 
                     INNER JOIN hot_price as hp ON hc.id_price_hot=hp.id
                    WHERE hc.id_zone  = 1 
                    AND  hc.time_start <= '$now'  
                    AND  hc.time_end >= '$now' 
                    AND ecoupon.delete_status = 0
                    AND restaurant.delete_status = 0 
                    group by ecoupon.id     
                    order by hp.top ASC ";
//         var_dump($query,$query1);die;
        $result=$this->db->query($query);
//        var_dump($result->result_array());die;
        $hot_co=$result->result_array();
        $number_hot=$result->num_rows();
         $string_sql="";
        //Lấy chuỗi string_hot_id để làm điều kiện truy vấn các restaurant không hot
        foreach ($hot_co as $item)
        {
//             $string_sql .= "(ecoupon.id != ".$item['id_coupon']." OR restaurant.id !=".$item['id_restaurant'] ." ) AND";
             $string_sql .= "ecoupon.id != ".$item['id_coupon']." AND ";
            //$item['id_coupon'] =$item['id_restaurant'];
            $array_hot_id[]=$item['id_coupon'];   
        } 
        $string_hot_id='('.implode(',', $array_hot_id).')';
//        var_dump($string_sql);die;
        if($number_hot == 0)$string_hot_id="('')";
//        var_dump($string_hot_id);die;
        $date=date('Y-m-d',  time()); 
        $unix_date = strtotime($date);
        $now=$unix_date;
        if($stt == 0)
        {
            $limit = $limit - $number_hot;
            //Khởi tạo mảng hot restaurant và chuyển mảng hot_res về mảng hot restaurant
            $hot_coupon = array(0 => '', 1 => '', 2 => '');
            foreach ($hot_co as $key => $value)
            {
                $hot_coupon[$value['top'] - 1] = $value;
            }
            
            //Lấy coupon ko hot(xếp theo 
            $query = "   SELECT ecoupon.id as id_coupon ,ecoupon.condition,ecoupon.content,ecoupon.content_sale_off,ecoupon.title AS name_coupon,ecoupon.img_thumbnail AS image_coupon,"."\n"
                        . "ecoupon.sale_off as money_coupon,ecoupon.date_user as expired_time, "."\n"
                        . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,GROUP_CONCAT(DISTINCT restaurant.name SEPARATOR ' , ') as name_restaurant ,GROUP_CONCAT(DISTINCT restaurant.id SEPARATOR ',') as id_restaurant  "."\n"
//                        . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,restaurant.name as name_restaurant  "."\n"
                        . "  FROM ecoupon INNER JOIN ecoupon_key ON ecoupon.id= ecoupon_key.id_ecoupon  "."\n"
                        . "INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id "."\n"
                        . "INNER JOIN restaurant_address ON restaurant.id=restaurant_address.restaurant_id "."\n"
                        . "WHERE restaurant_address.id_zone  = $zone AND ".$string_sql
                        . " ecoupon.date_user > '$now' "."\n"
                        . "AND ecoupon.amount_available < ecoupon.amount "."\n"
                        . "AND ecoupon.delete_status = 0 "."\n"
                        . "AND restaurant.delete_status = 0 "."\n"
//                         ." Group by ecoupon.id,restaurant.id"
                        ." Group by ecoupon.id "
                        . " order by ecoupon.id DESC ";// LIMIT 0,$limit";
            $sql_limit=" LIMIT 0,$limit";
            $coupon = $this->db->query($query.$sql_limit)->result_array();
            $total_nothot=$this->db->query($query)->num_rows();
            //Tách số lượng restaurant ở đầu để đấy lên trên
            $cut_coupon= array_slice($coupon, 0, 3 - $number_hot);
            $rest_coupon= array_slice($coupon, 3 - $number_hot);
//            var_dump($rest_restaurant,$cut_restaurant);
            $array = array(100);
            //Đẩy cut_coupon vào hot_coupon
            for ($i = 0; $i <= 2; $i++)
            {
                for ($j = 0; $j <= 2 - $number_hot; $j++)
                {
                    if ($hot_coupon[$i] == '' && !in_array($j,$array))
                    {     //(0 != 'abc' =>true)
                        $hot_coupon[$i] = $cut_coupon[$j];
//                        $only = $j;
                        $array[]=$j;
                        break;
                    }
                }
            }
//            var_dump($hot_coupon);die;
            $coupon = array_merge($hot_coupon, $rest_coupon);
            $total=$total_nothot + $number_hot;
            $data['list_coupon']=$this->setExpiredTime($coupon);
            $data['total']= $total;
            return (object)array('status' => 200, 'description' => 'Thành công','data'=>$data);
        }
        else 
        {
            $stt=$stt-$number_hot;
            $query = "SELECT ecoupon.id as id_coupon ,ecoupon.condition,ecoupon.content,ecoupon.content_sale_off,ecoupon.title AS name_coupon,ecoupon.img_thumbnail AS image_coupon,"."\n"
                        . "ecoupon.sale_off as money_coupon,ecoupon.date_user as expired_time, "."\n"
                        . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,GROUP_CONCAT(DISTINCT restaurant.name SEPARATOR ' , ') as name_restaurant ,GROUP_CONCAT(DISTINCT restaurant.id SEPARATOR ',') as id_restaurant  "."\n"
//                        . "ecoupon.amount,ecoupon.amount_available as coupon_dadat,restaurant.name as name_restaurant  "."\n"
                        . "  FROM ecoupon INNER JOIN ecoupon_key ON ecoupon.id= ecoupon_key.id_ecoupon  "."\n"
                        . "INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id "."\n"  
                        . " INNER JOIN restaurant_address ON restaurant.id=restaurant_address.restaurant_id "."\n"
                        . "WHERE restaurant_address.id_zone  = $zone AND ".$string_sql
                        . " ecoupon.date_user > '$now' "."\n"
                        . "AND ecoupon.amount_available < ecoupon.amount "."\n"
                        . "AND ecoupon.delete_status = 0 "."\n"
                        . "AND restaurant.delete_status = 0 "."\n"
                        ." Group by ecoupon.id"
//                        ." Group by ecoupon.id,restaurant.id"
                        . " order by ecoupon.id DESC ";// LIMIT $stt,$limit";
            $sql_limit = "  LIMIT $stt,$limit ";
            $coupon=$this->db->query($query.$sql_limit)->result_array();
            $total_nothot=$this->db->query($query)->num_rows();
            $total=$total_nothot + $number_hot;
//            $data['coupon']=$coupon;
            $data['list_coupon']=$this->setExpiredTime($coupon);
            $data['total']= $total;
            return (object)array('status' => 200, 'description' => 'Thành công','data'=>$data);
        }  
        //ecoupon.id NOT IN (3,5,6)    ecoupon.id !=3 ANd restaurant.id !
    }
    
    public function setExpiredTime($coupons)
    {
        foreach ($coupons as $key => $coupon)
                {
                    if($coupon == null)
                    {
                        unset($coupons[$key]);
                    }
                    else
                    {
                        $end= $coupon['expired_time']; 
                        $content=  str_replace(array('<div style="font-family: OpenSans;">','</div>'),'',$coupon['content']);
    //                    $end=1427621483;
    //                    $distance_time= $this->timeSince($start,$end,$units = 2);
                        $distance_time= $this->timeDiscount($end);
                        $coupons[$key]['expired_time']=date('d/m/Y',$end);
//                        $coupons[$key]['rest_time']=$distance_time;
                        $coupons[$key]['rest_time']=date('d/m/Y',$end);
                        $coupons[$key]['content']=$content;
                    }
                    
                }
        return $coupons;        
    }
    
    public function addressResDb($stringRes,$zone)
    {
//        var_dump($zone);die;
        If(in_array($stringRes, array(null, '', '0')) || in_array($zone, array(null, '', '0')))
        {
             return (object) array('status' => 201, 'description' => 'Tham số không đúng'); 
        }
        $checkZone=$this->db->query("SELECT id FROM zone WHERE id = $zone")->num_rows;
        if($checkZone == 0)return (object) array('status' => 201, 'description' => 'Không tìm thấy zone'); 
        $arrRes = explode(',',$stringRes);
//        var_dump($arrRes);die;
        if($arrRes == null)return (object) array('status' => 201, 'description' => 'Nhà hàng gửi lên không tồn tại'); 
        foreach ($arrRes as $res_id)
        {
            $name=$this->db->query("SELECT name from restaurant WHERE id = '$res_id'")->row()->name;
            if($name == null)
            {
                return (object)array('status' => 201, 'description' => 'Nhà hàng gửi lên ko tồn tại');
            }
            $query="SELECT ra.address,ra.latitude,ra.longitude FROM restaurant as r,restaurant_address as ra WHERE r.id=ra.restaurant_id AND r.delete_status=0 AND r.id= $res_id AND ra.id_zone = $zone";
//            var_dump($name,$query);die;
            $address=$this->db->query($query)->result_array();
            $arr['name']=$name;
            $arr['list_address']=$address;
            $data[list_restaurant][]=$arr;    
        }
        return (object) array('status' => 200, 'description' => 'Tìm thấy','data'=>$data); 
    }
    
    public function addressMycouponDb($user_id)
    {
        If(in_array($user_id, array(null, '', '0')))
        {
             return (object) array('status' => 201, 'description' => 'Tham số không đúng'); 
        }
        $date=date('Y-m-d',  time()); 
        $now = strtotime($date);
        $query  ="  SELECT ecoupon.id,FROM_UNIXTIME(ecoupon.date_user, '%d/%m/%Y') AS expried_date,ecoupon.title,restaurant_address.address,restaurant_address.latitude,restaurant_address.longitude,restaurant.name
                    FROM ecoupon INNER JOIN ecoupon_user ON ecoupon.id= ecoupon_user.coupon_id 
                    INNER JOIN ecoupon_key ON ecoupon.id= ecoupon_key.id_ecoupon  
                    INNER JOIN ecoupon_zone_key ON ecoupon.id=ecoupon_zone_key.id_coupon
                    INNER JOIN restaurant ON ecoupon_key.id_restaurant=restaurant.id 
                    INNER JOIN restaurant_address ON restaurant.id=restaurant_address.restaurant_id 
                    WHERE  ecoupon_user.user_id = '$user_id' AND ecoupon.delete_status = 0
                    AND ecoupon_zone_key.id_zone=restaurant_address.id_zone 
                    AND restaurant.delete_status = 0
                    AND ecoupon.delete_status = 0
                    AND ecoupon.active = 1 
                    AND ecoupon.date_user > '$now' ";
        $data=$this->db->query($query);
        if($data->num_rows() == 0)return (object)array('status' => 202, 'description' => 'Không tìm thấy');
        return (object)array('status' => 200, 'description' => 'Thành công,có data','data'=>$data->result_array());
//        return (object)$new_coupons;
    }
    
    public function getCouponWicityDb($code_coupon)
    {
        $result=$this->db->query("SELECT ecoupon_user.id,ecoupon_user.active,ecoupon_user.coupon_id,ecoupon_user.code_coupon,ecoupon_user.is_wicity FROM ecoupon_user WHERE code_coupon = '$code_coupon'" . ' LIMIT 1 ');
        if($result->num_rows() == 0) return (object) array('status' => 204, 'description' => 'Không tồn tại code coupon này bên wicard');
        $row_ecoupon_user = $result->row();
        if($row_ecoupon_user->is_wicity != 1) return (object) array('status' => 203, 'description' => 'Coupon này không phải của wicity');
        if($row_ecoupon_user->active != 0) return (object) array('status' => 203, 'description' => 'Coupon này đã nhận hoặc đã kích hoạt');
        //Nếu là của wicity
        if($row_ecoupon_user->is_wicity == 1) 
        {
            $coupon_id=$row_ecoupon_user->coupon_id;
            //Nếu thành công bên wicity thì mình insert về bên mình ,+1 amount,trả code ra màn hình
            $data = array('user_id' => 1,'active' => 1,'created' => date('Y-m-d H:i:s'));
            $this->db->where('id', $row_ecoupon_user->id);
            $result_update=$this->db->update('ecoupon_user', $data);     
            //thêm số lượng
            $amount_available = $this->db->query('SELECT amount_available FROM ecoupon WHERE id = '.$coupon_id)->row()->amount_available;
            $amount_available = (int)$amount_available + 1;
            $this->db->update('ecoupon', array( 'amount_available' => $amount_available ) , 'id = '.$coupon_id);
            return (object) array('status' => 200, 'description' => 'Thành công', 'code_coupon' => $code_coupon);
        }
    }
    
}
